package com.icim.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.icim.entity.His_ret_prd_info;
import com.icim.entity.Ret_wo;

@Repository
public class HisRetPrdInfoDao {
	@Resource
	private JdbcTemplate icimHisJdbcTemplate;

	
	private His_ret_prd_info setEntityByRs(ResultSet rs)
			throws SQLException {
		His_ret_prd_info hisRetPrdInfo = new His_ret_prd_info();
		hisRetPrdInfo.setPrd_seq_id(rs.getString("PRD_SEQ_ID"));
		hisRetPrdInfo.setPallet_id(rs.getString("PALLET_ID"));
		hisRetPrdInfo.setShip_box_id_fk(rs
				.getString("SHIP_BOX_ID_FK"));
		hisRetPrdInfo.setPrd_stat(rs.getString("PRD_STAT"));
		hisRetPrdInfo.setSta_time_d(rs.getDouble("STA_TIME_D"));
		hisRetPrdInfo.setEvt_cate(rs.getString("EVT_CATE"));
		hisRetPrdInfo.setEvt_usr(rs.getString("EVT_USR"));
		hisRetPrdInfo.setSht_ope_msg(rs
				.getString("SHT_OPE_MSG"));
		hisRetPrdInfo.setMdl_id_fk(rs.getString("MDL_ID_FK"));
		hisRetPrdInfo.setGroup_id(rs.getString("GROUP_ID"));
		hisRetPrdInfo.setMtrl_product_id(rs
				.getString("MTRL_PROD_ID_FK"));
		hisRetPrdInfo.setPrd_grade(rs.getString("PRD_GRADE"));
		hisRetPrdInfo.setCr_ope_id_fk(rs
				.getString("CR_OPE_ID_FK"));
		hisRetPrdInfo.setNx_ope_id_fk(rs
				.getString("NX_OPE_ID_FK"));
		hisRetPrdInfo.setCr_tool_id_fk(rs
				.getString("CR_TOOL_ID_FK"));
		hisRetPrdInfo.setNx_tool_id_fk(rs.getString("NX_TOOL_ID_FK"));
		hisRetPrdInfo.setLine_id(rs.getString("LINE_ID"));
		hisRetPrdInfo.setFab_id(rs.getString("FAB_ID"));
		hisRetPrdInfo.setProc_flg(rs.getString("PROC_FLG"));
		hisRetPrdInfo.setSo_id_fk(rs.getString("SO_ID_FK"));
		hisRetPrdInfo.setRsn_dept1(rs.getString("RSN_DEPT1"));
		hisRetPrdInfo.setDs_recipe_id(rs
				.getString("DS_RECIPE_ID"));
		hisRetPrdInfo.setOqc_skip_flg(rs
				.getString("OQC_SKIP_FLG"));
		hisRetPrdInfo.setRsn_cate1(rs.getString("RSN_CATE1"));
		hisRetPrdInfo.setEvt_timestamp(rs.getTimestamp("EVT_TIMESTAMP"));
		
		return hisRetPrdInfo;
	}
	
	
	public List<His_ret_prd_info> findLogonLogOffSheetsByWosTimes(List<Ret_wo> woList,
			Timestamp beginTimestamp, Timestamp endTimestamp) {

		StringBuffer sqlBuffer = new StringBuffer();

		sqlBuffer
				.append(" SELECT *  FROM HIS_RET_PRD_INFO ");

		sqlBuffer.append(" WHERE EVT_TIMESTAMP BETWEEN ? AND ? ");

		sqlBuffer.append(" AND EVT_CATE IN ('LGON','LGOF') ");
		if (woList.isEmpty()) {
			return new ArrayList<His_ret_prd_info>();
		}

		sqlBuffer.append(" AND WO_ID_FK IN ( ");
		for (Ret_wo retWo : woList) {
			sqlBuffer.append("'").append(retWo.getWo_id()).append("'");
			if (woList.indexOf(retWo) < woList.size() - 1) {
				sqlBuffer.append(",");
			}
		}
		sqlBuffer.append(")");

		return icimHisJdbcTemplate.query(sqlBuffer.toString(), new Object[] {
				beginTimestamp, endTimestamp },
				new RowMapper<His_ret_prd_info>() {

					@Override
					public His_ret_prd_info mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return setEntityByRs(rs);
					}
					
				});
	}

	public List<His_ret_prd_info> findLogoffRecordsByWosOpesTimes(List<Ret_wo> woList,
			String[] opeIds, Timestamp beginTimestamp, Timestamp endTimestamp) {

		StringBuffer sqlBuffer = new StringBuffer();

		sqlBuffer
				.append(" SELECT * FROM HIS_RET_PRD_INFO ");

		sqlBuffer.append(" WHERE EVT_TIMESTAMP BETWEEN ? AND ? ");

		sqlBuffer.append(" AND EVT_CATE IN ('LGOF') ");
		sqlBuffer.append(" AND CR_OPE_ID_FK IN ( ");
		for (int i = 0; i < opeIds.length; i++) {
			String opeId = opeIds[i];
			sqlBuffer.append("'").append(opeId).append("'");
			if (i < opeIds.length - 1) {
				sqlBuffer.append(",");
			}
		}
		sqlBuffer.append(" )");
		if (woList.isEmpty()) {
			return new ArrayList<His_ret_prd_info>();
		}

		sqlBuffer.append(" AND WO_ID_FK IN ( ");
		for (Ret_wo retWo : woList) {
			sqlBuffer.append("'").append(retWo.getWo_id()).append("'");
			if (woList.indexOf(retWo) < woList.size() - 1) {
				sqlBuffer.append(",");
			}
		}
		sqlBuffer.append(")");

		return icimHisJdbcTemplate.query(sqlBuffer.toString(), new Object[] {
				beginTimestamp, endTimestamp },
				new RowMapper<His_ret_prd_info>() {

					@Override
					public His_ret_prd_info mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return setEntityByRs(rs);
					}
				});
	}

	public List<His_ret_prd_info> listSheetHistory(String sheetId) {
		String sql = "SELECT * FROM HIS_RET_PRD_INFO WHERE PRD_SEQ_ID = ? ORDER BY EVT_TIMESTAMP ";
		return icimHisJdbcTemplate.query(sql, new Object[] { sheetId },
				new RowMapper<His_ret_prd_info>() {

					@Override
					public His_ret_prd_info mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return setEntityByRs(rs);
					}
				});
	}

	public Map<String, His_ret_prd_info> findSheetsStbiHistory(
			String[] sheetIds) {
		StringBuffer sqlBuffer = new StringBuffer(
				"SELECT * FROM HIS_RET_PRD_INFO WHERE PRD_SEQ_ID in (");
		int cnt = sheetIds.length;
		for (int i = 0; i < cnt; i++) {
			String sheetId = sheetIds[i];
			sqlBuffer.append("'").append(sheetId).append("'");
			if (i < cnt - 1) {
				sqlBuffer.append(",");
			}
		}
		sqlBuffer.append(") AND EVT_CATE = 'STBI'  ORDER BY PRD_SEQ_ID,EVT_TIMESTAMP");
		List<His_ret_prd_info> sheetsHistory = icimHisJdbcTemplate.query(
				sqlBuffer.toString(), new RowMapper<His_ret_prd_info>() {

					@Override
					public His_ret_prd_info mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return setEntityByRs(rs);
					}
				});

		Map<String, His_ret_prd_info> sheetHisMap = new HashMap<String, His_ret_prd_info>();

		for (His_ret_prd_info hisRetPrdInfo : sheetsHistory) {
			String prdSeqId = hisRetPrdInfo.getPrd_seq_id();
			sheetHisMap.put(prdSeqId,hisRetPrdInfo);
		}
		return sheetHisMap;

	}
	
	
	public Map<String, List<His_ret_prd_info>> findSheetsHistory(
			String[] sheetIds) {
		StringBuffer sqlBuffer = new StringBuffer(
				"SELECT * FROM HIS_RET_PRD_INFO WHERE PRD_SEQ_ID in (");
		int cnt = sheetIds.length;
		for (int i = 0; i < cnt; i++) {
			String sheetId = sheetIds[i];
			sqlBuffer.append("'").append(sheetId).append("'");
			if (i < cnt - 1) {
				sqlBuffer.append(",");
			}
		}
		sqlBuffer.append(")   ORDER BY PRD_SEQ_ID,EVT_TIMESTAMP");
		List<His_ret_prd_info> sheetsHistory = icimHisJdbcTemplate.query(
				sqlBuffer.toString(), new RowMapper<His_ret_prd_info>() {

					@Override
					public His_ret_prd_info mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return setEntityByRs(rs);
					}
				});

		Map<String, List<His_ret_prd_info>> sheetHisMap = new HashMap<String, List<His_ret_prd_info>>();

		for (His_ret_prd_info hisRetPrdInfo : sheetsHistory) {
			String prdSeqId = hisRetPrdInfo.getPrd_seq_id();
			List<His_ret_prd_info> list = sheetHisMap.get(prdSeqId);
			if(list==null){
				list = new ArrayList<His_ret_prd_info>();
			}
			list.add(hisRetPrdInfo);
			sheetHisMap.put(prdSeqId,list);
		}
		return sheetHisMap;

	}
}
